﻿use master
go

create database TestDB
on
(
	name=TestDB,
	filename='D:\SQL\TestDB.mdf',
	size=5MB,
	maxsize=100MB,
	filegrowth=10%
)
log on
(
	name=TestDB_ldf,
	filename='D:\SQL\TestDB.ldf',
	size=5MB,
	maxsize=100MB,
	filegrowth=10%
)

use TestDB
go

create table typelnfo
(
	typeld int primary key identity(1,1),
	typeName varchar(10) not null,
)

create table loginInfo
(
	LoginId int primary key identity(1,1),
	LoginName nvarchar(10) not null unique ,
	LoginPwd varchar(20) default(123456),
	LoginSex nvarchar(1) check(LoginSex='' or LoginSex='Ů'),
	LoginBir date ,
	LoginSort text not null
)

create database Company
on(
	name=Company,
	filename='D:\SQL\Company.mdf',
	size=5MB,
	maxsize=5MB,
	filegrowth=10%
)
 log on(
	name=Company_ldf,
	filename='D:\SQL\Company.ldf',
	size=5MB,
	maxsize=5MB,
	filegrowth=10%
)

use Company
go

create table SectionInfo
(
	SectionID int primary key identity(1,1) not null,
	SectionNamme varchar(10) not null,
)

create table UserInfo
(
	UserNo int identity(1,1) primary key not null,
	UserName varchar(10) unique not null check(len(UserName)>=4),
	UserSex varchar not null check(UserSex='' or Usersex='Ů'),
	UserAge int not null check(UserAge>=1 or UserAge<=100),
	UserAddress varchar(50) default(''),
	UserSection int foreign key  references SectionInfo(SectionID),
)

create table WorkInfo
(
	workld int identity(1,1) primary key not null,
	userId int foreign key references UserInfo(UserNo),
	WorkTime datetime Not null ,
	WorkDescription varchar(40) not null check(WorkDescription='ٵ' or WorkDescription='' or WorkDescription='' or WorkDescription='' or WorkDescription='¼')
)

create database StudentInfo
go
                
use StudentInfo
go

create table ClassInfo
(
	ClassID int primary key identity(1,1),
	ClassName nvarchar(10) not null unique,
	ClassTime  time not null,
	ClassDes text
)

create table StudentInfo
(
	StuID int primary key identity(1,1),
	StuName nvarchar(10) not null unique check(len(StuName)>2),
	StuSex nvarchar(2) not null  default('') check(StuSex='' or StuSex='Ů'),
	StuAdress varchar(12)  default('人'),
	ClassID int  foreign key references ClassInfo(ClassID)
)

create table CourseInfo
(
	CourseID int primary key identity(1,1),
	CourseName nvarchar(10) not null unique,
	CourseDes text
)

create table Score
(
	ScoreID int primary key identity(1,1),
	ScoreStuID int foreign key references StudentInfo(StuID),
	ScoreClassID int foreign key references ClassInfo(ClassID),
	Score int check(Score>=1 and Score<=100) not null
)

create database HomeInfo
go

use HomeInfo
go

create table TbIUser
(
	UserID int primary key identity(1,1),
	UserName nvarchar(10) not null,
	UserTel nvarchar(11) not null check(len(UserTel)=11)
)

create table tblHouseType
(
	TypelD int primary key identity(1,1),
	Typname nvarchar(10) not null check(Typname='' or Typname='ƽ' or Typname='ͨסլ' or Typname='')
)

create table TbIQx
(
	QxID int primary key identity(1,1),
	Qxname nvarchar(10) not null check(Qxname='' or Qxname='' or Qxname='')
)

create table TblHouseInfo
(
	HomeID int primary key identity(1,1),
	HomeDesc nvarchar(10),
	UserID int foreign key references  TbIUser(UserID),
	Homezj int not null,
	HomeShi int not null,
	HomeTing int not null,
	TypeID int foreign key references tblHouseType(TypelD),
	QxID int foreign key references TbIQx(Qxid)
)